package lumis.util;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import lumis.portal.PortalException;

/**
 * Utility class for JDBC operations.
 * 
 * @since 4.0.0
 * @version $Revision: 10458 $ $Date: 2009-06-02 15:49:09 -0300 (Tue, 02 Jun 2009) $
 */
public class JdbcUtil
{
	public static String prepareQueryParameter(String parameter) throws PortalException
	{
		return parameter.replaceAll("'", "''");
	}

	public static java.sql.Timestamp getTimestampByDate(Date data)
	{
		if (data == null)
			return null;
		else
			return new java.sql.Timestamp(data.getTime());
	}

	public static String[] fillStringArray(PreparedStatement statement, String fieldName) throws SQLException
	{
		List<String> rows = JdbcUtil.fillListString(statement, fieldName);
		return rows.toArray(new String[rows.size()]);
	}

	public static List<String> fillListString(PreparedStatement statement, String fieldName) throws SQLException
	{
		List<String> rows = new ArrayList<String>();
		ResultSet resultSet = statement.executeQuery();
		try
		{
			while (resultSet.next())
				rows.add(resultSet.getString(fieldName));			
		}
		finally
		{
			resultSet.close();			
		}
		return rows;
	}
	
	/**
	 * @deprecated Since 4.2.2.090330 replaced by {@link #generateInClause(String, int)}.
	 */
	@Deprecated
	public static String generateInClause(int numItems)
	{
		StringBuilder clause = new StringBuilder();
		for (int i = 0; i < numItems; i++)
		{
			if (clause.length() > 0)
				clause.append(",");
			clause.append("?");
		}
		clause.insert(0, " in (");
		clause.append(") ");
		return clause.toString();
	}
	
	/**
	 * Generates a SQL IN statement for a field. The IN statement is generated using <code>?</code> for each
	 * parameter, according to the <code>numItems</code> argument. 
	 * @param fieldName the field operand before the IN operator (e.g.: columnName or table.column).
	 * @param numItems the number of parameters to be included in the IN statement.
	 * @return the SQL code fragment for the IN statement.
	 * @since 4.2.2.090330
	 */
	public static String generateInClause(String fieldName, int numItems)
	{
		// validate arguments
		if(fieldName == null)
			throw new IllegalArgumentException("fieldName cannot be null");
		if(fieldName.length() == 0)
			throw new IllegalArgumentException("fieldName cannot be empty");
		if(numItems < 0)
			throw new IllegalArgumentException("numItems cannot be negative");
		
		// if numItems is zero returns a sql standard value for 'false'
		if (numItems == 0) 
			return "1=0";

		StringBuilder clause = new StringBuilder(2 + numItems * 2 + (fieldName.length() + 10) * (1 + numItems/1000));
		final int maxInElements = 1000;

		// open parentheses enclosing all
		clause.append("(");

		int index = 0;
		for (int i = 0; i < numItems; i++)
		{
			// if starting a new block
			if (index == 0)
			{	
				// if not the first block, append or
				if (i != 0)
					clause.append(") or ");
				
				// append IN beginning the block
				clause.append(fieldName + " in (");
			}

			// if not first ? in block, need comma
			if (index > 0)
				clause.append(",");
			
			// add ? for parameter
			clause.append("?");

			// if maximum element for IN reached, set index to zero to mark new block start
			if (index == maxInElements - 1)
				index = 0;
			else
				index++;
		}

		// close the last block and the first parentheses enclosing all
		clause.append("))");
		
		return clause.toString();
	}
}